As an alternative to utilizing Enersight’s default economic options, there is ability for the user to define a fiscal regime’s calculation logic within an excel document then upload this as a template for use by the software. This functionality enables capturing prior corporate investments into developing specialized modelling processes where nuance is required to correctly reflect the business problem beyond what is available within the pre-configured region based options. These spreadsheets can be attached to either the royalty module whereby its step-by-step determined burdens feed back into the economic limit calculation or the tax module whereby it is able to determine any element outcome at the end of the calculation, though typically it would be utilized for derivation of tax and after-tax elements.
Enersight Calculation Sequence Logic & Integration of Excel
In accordance with the general Enersight calculation sequence logic, both Royalty and Tax/Reporting spreadsheets can see the same PreTax information, however only Tax/Reporting may see depreciation.
Click image to expand or minimize.
Scope of Capability
Both methods allow for bringing in any variable within Enersight, enabling temporary calculation then returning variables from the spreadsheet. These outputs may be the same or different to the input variables. As noted, Royalty may only return burdens, whilst the Tax/Reporting module may return any variable. Additionally, these non-tax associated elements are unable to be rolled up within the network structure (ATAX Cashflow will however roll up).
Setting up the Economic Model
When setting up the economic model to accept excel templates, the key elements are Econ_ExcelRoyalties and/or Econ_ExcelTaxReporting to enable attaching an excel template for the Royalties or Tax/Reporting calculations respectively. Additional options include being able to have standard tax and/or other royalty calculations preceding the Excel versions within the model to enable pre-calculation using their pre-configured optionality or separate calculation paths if the excel based nuance only occurs within certain assets of the model.
If capital inputs are required within the Royalty module, Econ_AsEnteredCapitalByClass must be placed prior to it within the calculation order. For the Tax module, the economic model may also require addition of either: Econ_AsEnteredCapitalByClass or Econ_NoTaxFullCapitalCalcs if capital and/or depreciation required to perform the desired calculations.
If Econ_Last is included as the last calculation element within the Economic Model, then Tax and ATAX elements will roll up through the calculation. This is a required element in all other cases.
Click image to expand or minimize.
Documents
Excel templates must be uploaded into Enersight via Administrator / Documents and must have a unique name. This repository includes all documents as well as debug files which may be generated from runs.
Upon upload, the user must specify if they want the file to be public or not. Where the user is updating a currently existing document they must explicitly specify to update and replace the template, or the upload will fail on validation.
Click image to expand or minimize.
Ringfencing and Multiple Spreadsheets
Within both royalty and taxation methods, you can ringfence groups of assets together to pool their input values for evaluation. A ringfence grouping is defined via identical user data inputs when assets exist within the same inheritance tree. The Outs of the template are then distributed amongst all assets belonging to the ringfence based upon a user defined function, on a per attribute basis. In the absence of such inputs, the presumption is that the Outs are evenly allocated amongst all ringfenced assets, thus a common function may be =@HasProduced() so as to only allocate amongst contributing assets.
When managing different ringfences, two options exist to enable appropriate capturing of nuance:
- May utilize the Parameters sheet within the template to shape a single spreadsheet’s functionality or calculation process with if statements as necessary
- Multiple spreadsheet templates may be attached and inherited by the appropriate contributing assets which are intrinsically shaped as required to capture the detail.
Assigning Templates to Assets
Templates can either be directly assigned to an asset or inherited along with other calculation attributes as appropriate to whether they are Royalty or Tax/Reporting.
In both cases, ring fence details can be specified as either inactive or grouped upon a selected User Data Field. Outs may be allocated with a function based definition or alternatively set to pass through,
whereby the outcome is repeated for each contributing asset in the case of ringfencing i.e. where a ratio is the outcome, and debug spreadsheets can be called.
For Royalties, the spreadsheet can be defined at a facility and inherited subsequently from associated wells.
Click image to expand or minimize.
For Tax and Reporting, there are multiple other options as to whether the calculation should proceed within the asset, and how. A tooltip based upon the selection of the top boxes describes the outcome, whether there is calculation of tax or passing on of inflow as appropriate. Within the Calculations themselves, the spreadsheet can be defined at any asset and inherited subsequently from associated assets. Where tax has been defined at a mid-stream asset, downstream elements may be set automatically to not further calculate taxes, but rather inherit inflowing results from the calculations performed at this asset. Finally, there are options to either use rollup (default) or incremental results for feeding the IN to the template as well as whether OUT should replace (default) or add to existing results. It is noted that the use of incremental results is primarily intended for where a ringfence includes members from multiple levels of the network, thus allowing capital at facilities to also contribute to well level production.
Click image to expand or minimize.
Debug Spreadsheets
Debug spreadsheets may be created for both Royalty and Tax / Reporting calculations. These spreadsheets are the time expanded template including the Ins, Outs and Temporary data for the ringfence or single asset specified for analysis. Within a single scenario only one of each type may be defined. Debug spreadsheets created through the calculation are stored within the Documents section.
For royalty spreadsheets, define by specifying the desired Asset within the Royalty input field at the scenario level, ticking the box to include it and the timestep that the evaluation would be done on. If within a ringfence then specifying any of the assets within the ringfence will provide the outcome for all combined – pre-backallocation to the individual contributors.
For Tax and Reporting the debug spreadsheet is defined differently pending whether a ring fence or single asset is desired, using the nomenclature below:
Ring fence / [Asset Name] / [User Data Label] [User Data Input] / [excel document name]
- e.g. ring fence / Facility / ATP A / Excel Testing.xlsx
Single asset / [Asset Name] / [excel document name]
- e.g. single asset / Facility / Excel Testing.xlsx
Click image to expand or minimize.
Template Structure
At its most basic, an excel template must include a specially formatted tab labelled ‘PreTax’ which handles the interaction with Enersight and is scalable to the time horizon. A secondary structured tab labelled ‘Parameters’ may also be included for use in controlling inputs and assumptions. This tab can pull project and scenario level user data inputs from the calculation (non-formulaic). Additionally, any number of extra tabs may be added to the template which are freeform in structure and may be used either as references or as a calculation engine. If these extra tabs are utilized for calculating per timestep
they will need to be pre-defined for the entire range of possible calculation horizons for which they may be utilized as unlike the ‘PreTax’ sheet. The distinct structure shown of the ‘PreTax’ sheet is explained below:
A. Type: contains arguments of:
- IN = inputs
- OUT = outputs
- TMP = temporary calculation step with a persistent function per timestep
IN formulas are just placeholders for calculation numbers whilst OUT formulae can combine math, Boolean operations, percent etc. of specific temporary or in elements
B. Unit: corresponds to the unit of the argument type, relevant for IN and OUT only. Where incorrect or no units have been specified Metric Medium will be the presumption.
C. Element: corresponds to the specific excel syntax required to call or push data for IN and OUT arguments respectively. Refer to Excel Economics--Functions for exact syntax. For TMP it provides a place to comment or label as desired with no impacts on calculation.
D. Title: corresponds to the specific excel syntax required to call or push data for IN and OUT arguments respectively in conjunction with Element. Refer to Excel Economics--Functions for exact syntax. For TMP it provides a place to comment or label as desired with no impacts on calculation.
E. Date: corresponds to the specific time step that the calculation is to be implemented.
- Formulas may change per timestep
- The last month’s input formulae is persistently utilized for any expansion to the template horizon that occurs due to calculation period
F. Formulae: Calculation process to be utilized, made up of:
- Basic mathematical operations i.e. addition, subtraction, multiplication, division and exponential
- If statements and Boolean capabilities
- Able to utilize a large array of excel functionality including Index, Match, VLookup
- Full flexibility in structure such that an IN for one product may modify an OUT for a completely different product
The ‘Parameters’ sheet follows a similar structure to the ‘PreTax’ with the exception that rather than dates a single value is available for import. Inputs to this sheet are non-formulaic compatible.
Limitations
Royalty calculated on a timestep by timestep basis, thus only burden results from the current timestep for each calculation are returned. If there is complexity within the spreadsheet that changes historical results, these outcomes are discarded, however this may cause issues when analyzing debug sheets.
Due to calculation logic restrictions, only tax and after-tax element changed via the spreadsheet will roll up within the calculation process and only if Econ_Last is included as the last calculation element within the Economic Model. If using the Excel Template to redefine / override other data elements then the outcomes will be held only within the assets they are backallocated to as part of the ringfencing.
When newly created Company Level User Data is utilized for a Parameter inputs, it will not be visible until after the data has been cached on the database for the first time (up to 6 hours after initially created).
When doing division need to ensure that calculation order is appropriate such that divisors are available to avoid divide by 0 within the first pass. It is thus recommended to add a +0.00001 term to the function.